package com.dp.appi.dao.impl;

import com.dp.appi.dao.ChannelDao;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * Created by xiaoji on 2016/5/16.
 * 通道特征点
 */
@Component
public class ChannelDaoImpl extends BaseDaoImpl implements ChannelDao {
    /**
     * 通道特征点(有下级)列表
     *
     * @param exploration_id 当前踏勘id
     * @return
     */
    @Override
    public List<Map<String, Object>> channelAList(Long exploration_id) {
        String sql = "select pc.channel_id,fea.pt_id,fea.pt_objectid,fea.distance,fea.exploration_id,fea.number from p_channel pc " +
                " INNER JOIN p_exploration fea ON fea.exploration_id=pc.feature_id " +
                " LEFT JOIN p_pt pt on fea.pt_id=pt.pt_id " +
                " where pc.exploration_id=? and pt.pt_type = 12  and pt.is_deleted = 0 and fea.deleted = 0 ORDER BY fea.distance asc  ";
        return getJdbcTemplate().queryForList(sql, new Object[]{exploration_id});
    }

    /**
     * 通道特征点（无上级）列表
     *
     * @param exploration_id
     * @return
     */
    @Override
    public List<Map<String, Object>> channelList(Long exploration_id) {
//        String sql1="select pex.exploration_id,pex.pt_id,pex.pt_objectid,pex.distance,pex.number from p_exploration pex " +
//                " LEFT JOIN p_pt pt on pex.pt_id=pt.pt_id " +
//                " where (pex.stage_id,pex.classify) in ( " +
//                "select pex2.stage_id,pex2.classify from p_exploration pex2 where pex2.exploration_id=? " +
//                ") and pex.before_exp is null and pt.pt_type = 12 and pt.is_deleted = 0 and pex.deleted = 0 ORDER BY pex.distance asc  " ;
//        StringBuffer sql = new StringBuffer();
//        sql.append("select pex.stage_id,pex.exploration_id,pex.pt_id,pex.pt_objectid,pex.distance,pex.number from p_exploration pex ");
//        sql.append("LEFT JOIN p_pt pt on pex.pt_id=pt.pt_id ");
//         sql.append("LEFT JOIN p_channel pc on pex.exploration_id = pc.feature_id ");
//        sql.append("where pc.channel_id is null and pt.pt_type = 12 and pt.is_deleted = 0 and pex.deleted = 0 ");
//        sql.append("and (pex.before_exp,pex.stage_id,pex.classify) in ");
//        sql.append("(select pex2.before_exp,pex2.stage_id,pex2.classify from p_exploration pex2 where pex2.exploration_id=? and pex2.before_exp is not null) ");
//        sql.append("ORDER BY pex.distance asc ; ");
        String sql = "select p1.stage_id,p1.exploration_id,p1.pt_id,p1.pt_objectid,p1.distance,p1.number from p_exploration p1\n" +
                "inner JOIN p_exploration p2 on p1.before_exp = p2.exploration_id\n" +
                "inner JOIN p_pt p3 on (p3.pt_id = p1.pt_id and p3.pt_type=12)\n" +
                "where p1.before_exp = ? \n" +
                "and p1.deleted=0 and p1.exp_input=1\n" +
                "and p2.deleted=0\n" +
                "and p1.exploration_id not in(select p4.feature_id from p_channel p4)\n" +
                "ORDER BY p1.distance asc ";

        return getJdbcTemplate().queryForList(sql, new Object[]{exploration_id});
    }

    /**
     * 关联通道特征信息
     *
     * @param exploration_id 特征点踏勘id
     * @param after_exp      下级级联
     * @return
     */
    @Override
    public Long corrChannelFeature(final Long exploration_id, final Long after_exp) {
        final String sql = "insert into p_channel(exploration_id,feature_id,create_time) values(?,?,NOW())";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, after_exp);
                ps.setLong(2, exploration_id);
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 添加特征点
     *
     * @param param
     * @return
     */
    @Override
    public Long addPexploration(final Map<String, Object> param) {
        final String sql = "insert into p_exploration(`number`,before_exp,exploration_person,exploration_type,pt_id," +
                "stage_id,distance,explor_id,classify,exp_input,voltage_grade)" +
                " values(?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, String.valueOf(param.get("number")));
//                ps.setLong(2, param.get("before_exp")==null?null:Long.parseLong(String.valueOf(param.get("before_exp"))));
                if (param.get("before_exp") == null) ps.setNull(2, java.sql.Types.NULL);
                else ps.setLong(2, Long.parseLong(String.valueOf(param.get("before_exp"))));

                ps.setString(3, String.valueOf(param.get("exploration_person")));
                ps.setInt(4, Integer.parseInt(String.valueOf(param.get("exploration_type"))));

                if (param.get("pt_id") == null) ps.setNull(5, java.sql.Types.NULL);
                else ps.setLong(5, Long.parseLong(String.valueOf(param.get("pt_id"))));

                if (param.get("stage_id") == null) ps.setNull(6, java.sql.Types.NULL);
                else ps.setLong(6, Long.parseLong(String.valueOf(param.get("stage_id"))));

                if (param.get("distance") == null) ps.setNull(7, java.sql.Types.NULL);
                else ps.setFloat(7, Float.parseFloat(String.valueOf(param.get("distance"))));

                if (param.get("explor_id") == null) ps.setNull(8, java.sql.Types.NULL);
                else ps.setLong(8, Long.parseLong(String.valueOf(param.get("explor_id"))));

                if (param.get("classify") == null) ps.setNull(9, java.sql.Types.NULL);
                else ps.setByte(9, Byte.valueOf(String.valueOf(param.get("classify"))));

                ps.setInt(10, 0);

                if (param.get("voltage_grade") == null) ps.setNull(11, java.sql.Types.NULL);
                ps.setInt(11, Integer.parseInt(String.valueOf(param.get("voltage_grade"))));

                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 获取踏勘点信息
     *
     * @param id 踏勘点id
     * @return
     */
    @Override
    public Map<String, Object> getPexplorationById(Long id) {
        String sql = "select * from p_exploration where exploration_id=?";
        return findOneObject(sql, new Object[]{id});
    }

    /**
     * 查询特征点扩展信息
     *
     * @param channel_id
     */
    @Override
    public Map<String, Object> queryChannelFeature(long channel_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select pt_table,pt_objectid from p_channel ch ");
        sql.append("left join p_exploration exp on ch.feature_id = exp.exploration_id ");
        sql.append("left join p_pt pt on pt.pt_id = exp.pt_id ");
        sql.append("where ch.channel_id = ? and exp.deleted = 0 ");
        Map<String, Object> res = getJdbcTemplate().queryForMap(sql.toString(), channel_id);

        StringBuffer ksql = new StringBuffer();
        ksql.append("select * from ");
        ksql.append(res.get("pt_table"));
        ksql.append(" where id = ?");

        Map<String, Object> kres = queryForMap(ksql.toString(), res.get("pt_objectid"));
        return kres;
    }

    /**
     * 获取踏勘特征点信息
     *
     * @param channelId
     * @return
     */
    @Override
    public Map<String, Object> queryExpByChannelId(Long channelId) {
        StringBuffer ksql = new StringBuffer();
        ksql.append("select pc.channel_id,pex.exploration_id,pex.exp_input,pt.pt_table,pex.pt_objectid  ");
        ksql.append("from p_channel pc ");
        ksql.append("left join p_exploration pex ");
        ksql.append("on pc.feature_id = pex.exploration_id ");
        ksql.append("left join p_pt pt ");
        ksql.append("on pt.pt_id = pex.pt_id ");
        ksql.append("where channel_id = ?");
        Map<String, Object> kres = queryForMap(ksql.toString(), channelId);
        return kres;
    }

    /**
     * 删除踏勘特征点信息
     *
     * @param feature_id
     */
    @Override
    public void delExploration(long feature_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM p_exploration ");
        sql.append(" where exploration_id = ?");
        getJdbcTemplate().update(sql.toString(), feature_id);
    }

    /**
     * 删除通道特征信息
     *
     * @param channel_id 通道id
     */
    @Override
    public void delChannelFeature(Long channel_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM p_channel ");
        sql.append(" where channel_id = ?");
        getJdbcTemplate().update(sql.toString(), channel_id);
    }
}
